﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using QXBook.Model;
using QXBook.Utils;
using System.Data.Common;
using System.Data.SqlClient;

namespace QXBook.Service
{
    /// <summary>
    /// 视频相关的业务逻辑
    /// </summary>
    public class VideoService : ServiceBase
    {
        public void DeleteList(string ids)
        {
            string sql = "DELETE FROM CoreTemplate WHERE CoreID IN (SELECT CoreID FROM Viedo WHERE VideoID IN (" + ids + "))";
            sql += "DELETE FROM Viedo WHERE VideoID IN (" + ids + "))";
            this.db.ExecuteNonQuery(CommandType.Text, sql);
        }

        public VideoInfo GetModel(int videoID)
        {
            string sql = "SELECT A.Title,A.Content,A.AddDate,B.VideoID,B.CoreID,B.VideoPath,B.VideoUrl,B.VideoType,B.IsNeedScore,B.Score FROM CoreTemplate A JOIN Video B ON B.CoreID=A.CoreID WHERE B.VideoID=" + videoID;

            DataSet ds = this.db.ExecuteDataSet(CommandType.Text, sql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                VideoInfo video = new VideoInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    video.VideoID = CommonUtils.ToInt(dr["ViedoID"]);
                    video.CoreID = CommonUtils.ToInt(dr["CoreID"]);
                    //发布人
                    video.Author = CommonUtils.ToString(dr["Author"]);
                    video.Title = CommonUtils.ToString(dr["Title"]);
                    video.Content = CommonUtils.ToString(dr["Content"]);
                    video.VideoPath = CommonUtils.ToString(dr["VideoPath"]);
                    video.VideoUrl = CommonUtils.ToString(dr["VideoUrl"]);
                    video.VideoType = (VideoType)CommonUtils.ToInt(dr["VideoType"]);
                    video.IsNeedScore =(YesNo)CommonUtils.ToInt(dr["IsNeedScore"]);
                    video.Score = CommonUtils.ToFloat(dr["Score"]);
                    video.AddDate = CommonUtils.ToTime(dr["AddDate"].ToString());
                }

                return video;
            }
            return null;
        }

        public string AddVideo(string AdminName, VideoInfo video)
        {
            MembersInfo admininfo = ServiceFactory.CreateInstance<AdminService>().GetAdminEntityByAdminName(AdminName);
            //判断当前管理员是否存在
            string rtmessage = ServiceFactory.CreateInstance<AdminService>().CheckAdminPower(admininfo);
            if (!string.IsNullOrEmpty(rtmessage)) return rtmessage;

            DbCommand cmd = new SqlCommand();
            cmd.CommandText = @"INSERT INTO CoreTemplate(Title,CategoryID,Content,Author,SmallImg,BigImg,IsPublish,AddDate) 
                                VALUES (@Title,@CategoryID,@Content,@Author,@SmallImg,@BigImg,@IsPublish,GETDATE());SELECT @@IDENTITY";//返回主键

            base.db.AddInParameter(cmd, "@Title", DbType.String, video.Title);
            base.db.AddInParameter(cmd, "@CategoryID", DbType.String, video.CategoryID);
            base.db.AddInParameter(cmd, "@Content", DbType.String, video.Content);
            base.db.AddInParameter(cmd, "@Author", DbType.String, video.Author);
            base.db.AddInParameter(cmd, "@SmallImg", DbType.String, video.SmallImg);
            base.db.AddInParameter(cmd, "@BigImg", DbType.String, video.SmallImg);
            base.db.AddInParameter(cmd, "@IsPublish", DbType.Int16, (int)video.IsPublish);

            int identity = 0;
            object obj = base.db.ExecuteScalar(cmd);
            if (obj != null)
            {
                identity = CommonUtils.ToInt(obj);
            }

            cmd.CommandText = @"INSERT INTO Video(CoreID,VideoPath,VideoUrl,VideoType,IsNeedScore,Score) 
                                VALUES (@CoreID,@VideoPath,@VideoUrl,@VideoType,@IsNeedScore,@Score)";

            base.db.AddInParameter(cmd, "@CoreID", DbType.Int32, identity);
            base.db.AddInParameter(cmd, "@VideoPath", DbType.String, video.VideoPath);
            base.db.AddInParameter(cmd, "@VideoUrl", DbType.String, video.VideoUrl);
            base.db.AddInParameter(cmd, "@VideoType", DbType.Int16, (int)video.VideoType);
            base.db.AddInParameter(cmd, "@IsNeedScore", DbType.Int16, (int)video.IsNeedScore);
            base.db.AddInParameter(cmd, "@Score", DbType.String, video.Score.ToString());

            base.db.ExecuteNonQuery(cmd);
            return string.Empty;
        }

        public string UpdateVideo(string AdminName, VideoInfo video)
        {
            MembersInfo admininfo = ServiceFactory.CreateInstance<AdminService>().GetAdminEntityByAdminName(AdminName);
            //判断当前管理员是否存在
            string rtmessage = ServiceFactory.CreateInstance<AdminService>().CheckAdminPower(admininfo);
            if (!string.IsNullOrEmpty(rtmessage)) return rtmessage;

            string SQL = "UPDATE CoreTemplate SET ";
            SQL += "Title=@Title,CategoryID=@CategoryID,Content=@Content,Author=@Author,SmallImg=@SmallImg,BigImg=@BigImg,IsPublish=@IsPublish,UpdateDate=GETDATE() WHERE CoreID=@CoreID";

            DbCommand cmd = new SqlCommand(SQL);
            base.db.AddInParameter(cmd, "@Title", DbType.String, video.Title);
            base.db.AddInParameter(cmd, "@CategoryID", DbType.Int32, video.CategoryID);
            base.db.AddInParameter(cmd, "@Content", DbType.String, video.Content);
            base.db.AddInParameter(cmd, "@Author", DbType.String, video.Author);
            base.db.AddInParameter(cmd, "@SmallImg", DbType.String, video.SmallImg);
            base.db.AddInParameter(cmd, "@BigImg", DbType.String, video.BigImg);
            base.db.AddInParameter(cmd, "@IsPublish", DbType.Int16, (int)video.IsPublish);
            base.db.AddInParameter(cmd, "@CoreID", DbType.Int32, video.CoreID);
            base.db.ExecuteNonQuery(cmd);

            cmd.CommandText = @"UPDATE Video SET 
                    VideoPath=@VideoPath,VideoUrl=@VideoUrl,VideoType=@VideoType,IsNeedScore=@IsNeedScore,Score=@Score WHERE VideoID=@VideoID";
            base.db.AddInParameter(cmd, "@VideoPath", DbType.String, video.VideoPath);
            base.db.AddInParameter(cmd, "@VideoUrl", DbType.String, video.VideoUrl);
            base.db.AddInParameter(cmd, "@VideoType", DbType.String, (int)video.VideoType);
            base.db.AddInParameter(cmd, "@IsNeedScore", DbType.Int16, (int)video.IsNeedScore);
            base.db.AddInParameter(cmd, "@Score", DbType.String, video.Score.ToString());
            base.db.AddInParameter(cmd, "@VideoID", DbType.Int32, video.VideoID);

            return string.Empty;
        }
        /// <summary>
        /// 获取视频信息
        /// </summary>
        /// <param name="ps"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public List<VideoInfo> GetModelList(ref PagerState ps, VideoCondition condition)
        {
            List<VideoInfo> list = new List<VideoInfo>();
            DbCommand cmd = new SqlCommand();

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT A.VideoID,A.CoreID,A.IsNeedScore,A.Score,B.Title,B.SmallImg,B.IsPublish,B.AddDate,B.UpDateDate FROM CoreTemplate B JOIN Video A ON B.CoreID=A.CoreID");
            sbSql.Append(" LEFT JOIN Category C ON B.CategoryID=C.CategoryID ");
            sbSql.Append(" WHERE 1=1");
            if (condition != null)
            {
                if (condition.IsPublish != YesNo.Unknow)
                {
                    sbSql.Append(" AND B.IsPublish=" + (int)condition.IsPublish);
                }

                if (condition.VideoType != VideoType.Unknow)
                {
                    sbSql.Append(" AND B.VideoType=" + (int)condition.VideoType);
                }
            }

            DataTable dt = this.GetListPager(ref ps, sbSql.ToString(), "VideoID", cmd);

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    VideoInfo video = new VideoInfo();
                    video.VideoID = CommonUtils.ToInt(dr["VideoID"]);
                    video.Title = CommonUtils.ToString(dr["Title"]);
                    video.IsNeedScore = (YesNo)CommonUtils.ToInt(dr["IsNeedScore"]);
                    video.Score = CommonUtils.ToFloat(dr["Score"]);
                    video.IsPublish = (YesNo)CommonUtils.ToInt(dr["IsPublish"]);
                    video.AddDate = CommonUtils.ToTime(dr["AddDate"].ToString());
                    video.UpdateDate = CommonUtils.ToTime(dr["UpdateDate"].ToString());
                    list.Add(video);
                }
            }

            return list;
        }

        /// <summary>
        /// 根据年-月获取数据
        /// </summary>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetDataByMonth(DateTime month)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("VideoID");
            dtResult.Columns.Add("Title");
            dtResult.Columns.Add("URL");
            dtResult.Columns.Add("CalendarDate");
            DbCommand cmd = new SqlCommand();

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT A.VideoID,A.CoreID,A.IsNeedScore,A.Score,B.Title,B.SmallImg,B.IsPublish,B.AddDate,B.UpDateDate FROM CoreTemplate B JOIN Video A ON B.CoreID=A.CoreID");
            sbSql.Append(" LEFT JOIN Category C ON B.CategoryID=C.CategoryID ");
            sbSql.Append(" WHERE 1=1");
            if (month != null)
            {
                DateTime startMonth = month.AddDays(1 - month.Day);   //本月月初
                DateTime endMonth = startMonth.AddMonths(1).AddDays(-1);   //本月月末
                sbSql.Append(" AND B.AddDate>='" + startMonth.ToString("yyyy/MM/dd") + "' AND B.AddDate <'" + endMonth.ToString("yyyy/MM/dd") + "'");
            }
            cmd.CommandText = sbSql.ToString();

            DataSet ds = base.db.ExecuteDataSet(cmd);
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    DataRow drNew = dtResult.NewRow();
                    drNew["VideoID"] = CommonUtils.ToInt(dr["VideoID"]);
                    drNew["Title"] = CommonUtils.ToString(dr["Title"]);
                    drNew["URL"] = "Video_View.aspx?VideoID=" + CommonUtils.ToString(dr["VideoID"]);
                    drNew["CalendarDate"] = CommonUtils.ToTime(dr["AddDate"].ToString());
                    dtResult.Rows.Add(drNew);
                }
                return dtResult;
            }

            return null;
        }
    }
}
